package com.student.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.student.entity.StudentInfo;

//操作 数据库的通用的方法写在此包中
public class DBUtil {
	private static final String URL ="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private static final String UNAME = "root";
	private static final String PWD = "123456";
	//1.将两大类的公有属性提取，2.方便在其他类的方法调用
	public static Connection con = null;
	public static PreparedStatement pst = null;
	public static ResultSet rs = null;

//查询总数
	public static int getTotalCount(String sql){
		int count = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(URL, UNAME, PWD);
			
	//	sql = "update tablename set sname=?,sage=?,saddress=? where sno=? ";
		
		pst = con.prepareStatement(sql);

		rs = pst.executeQuery();
		if(rs.next()){
			count = rs.getInt(1);
		}
		
		}catch( ClassNotFoundException e){
			e.printStackTrace();
			
		}catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			try{
			if(rs!=null) rs.close();	
			if(pst!=null) pst.close();
			if(con!=null) con.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return count;	
	}
	
	
	//分以下两大类 --> 1. 增删改
	public  static boolean  executeUpdate (String sql, Object[] params){
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(URL, UNAME, PWD);
			
	//	sql = "update tablename set sname=?,sage=?,saddress=? where sno=? ";
		
		pst = con.prepareStatement(sql);
		
		for(int i=0;i<params.length;i++){
			pst.setObject(i+1,params[i]);
		}
		
		int cont = pst.executeUpdate();
		if(cont>0){
			return true;}
		else{
			return false;
		}
		
		}catch( ClassNotFoundException e){
			e.printStackTrace();
			return false;
		}catch (SQLException e) {
			e.printStackTrace();
			return false;
		}finally{
			try{
			if(pst!=null) pst.close();
			if(con!=null) con.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}	
	}
	
	//--> 2.通用的查  返回一个结果集合 ，适合任意查询
	public static ResultSet queryAll(String sql, Object[] params){
		
		StudentInfo stu = null;
		List<StudentInfo> stuall = new ArrayList<>();
		
		try{
			
		Class.forName("com.mysql.jdbc.Driver");
		con = DriverManager.getConnection(URL, UNAME, PWD);
	
		pst = con.prepareStatement(sql);
		
		if(params!=null){
		for(int i=0;i<params.length;i++){
			pst.setObject(i+1,params[i]);
			}
		}
		
		rs = pst.executeQuery();
		
/*		while(rs.next()){
			int no = rs.getInt("sno");
			String name = rs.getString("sname");
			int age = rs.getInt("sage");
			String address = rs.getString("saddress");
			stu = new Student(no, name, age, address);
			stuall.add(stu);
		}
*/		
		return rs;
		
		}catch( ClassNotFoundException e){
			e.printStackTrace();
			return null;
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
/*		finally{
			try{
			if(rs!=null) rs.close();
			if(pst!=null) pst.close();
			if(con!=null) con.close();
			}catch(SQLException e){
				e.printStackTrace();
			} //需要放返回结果集，因此先不关闭
*/			
		}
	}
